<?php
  
/**
 * Export measurements from Munisense using the REST Webservice
 * and import it in a SQL table.
 * 
 * @author Joost Pastoor <joost.pastoor@munisense.com>
 */
 
/**
 * Configuration
 */ 
// Connection config to the Munisense Portal
$muni_host = "yourportalname.munisense.net";
$muni_user = "";
$muni_pwd = ""; 
 
// Connection config to the destination database
$db_host = "localhost";
$db_user = "";
$db_pwd = "";
$db_dbname = "";
$db_tablename = "measurements";

// The Domainobjects and their properties to fetch
// Format: Array containing arrays with object_id, object_type, object_property
$targets = array(
  array("object_id" => 45, "object_type" => "DOSoundMeasurementPoint", "object_property" => "laeq"),
  array("object_id" => 44, "object_type" => "DOMeteo", "object_property" => "wind_direction"),
);

// Start Time from which measurements should be fetched
$start_day = 20;
$start_month = 11;
$start_year = 2012;       
                                    
/* End of Configuration */

// Avoid PHP timeouts if the queries take long
set_time_limit(0);

/**
 * Determine Start Timestamp of query based on the last stored timestamp
 * in the local database table. It uses that timestamp to continue with
 * querying additional data.
 */
 connectDatabase($db_host, $db_user, $db_pwd, $db_dbname);
 prepareTables($db_tablename);
 
 $r_last_saved_timestamps = mysql_query("
  SELECT `object_id`, `object_property`, max(`timestamp`) as `last_timestamp`
  FROM `".$db_tablename."`
  GROUP BY `object_id`, `object_property`
") or die("Could fetch latest timestamps: " . mysql_error()); 

// Create array with last timestamps
$last_timestamps = array();
while($row = mysql_fetch_assoc($r_last_saved_timestamps)) {
  $last_timestamps[$row['object_id']][$row['object_property']] = $row['last_timestamp'] + 1000;
}

// If there is no data present yet, use the configured start date
$default_start_timestamp = mktime(0, 0, 0, $start_month, $start_day, $start_year) * 1000;

// Loop over the targets
foreach($targets as $target_info) {
  $object_id = $target_info['object_id'];
  $object_type = $target_info['object_type'];
  $object_property = $target_info['object_property'];
 
  // If the target has no last timestamp
  if(!isset($last_timestamps[$object_id][$object_property])) { 
    /// .. use the configured start time
    $last_timestamps[$object_id][$object_property] = $default_start_timestamp; 
  }
}

/**
 * Query the data from the Munisense REST webservice
 */
foreach($targets as $object_id => $target_info) {
  $object_id = $target_info['object_id'];
  $object_type = $target_info['object_type'];
  $object_property = $target_info['object_property'];

  $results = queryWebservice($muni_host, $muni_user, $muni_pwd, $object_type, $object_property, $object_id, $last_timestamps[$object_id][$object_property]);
  
  // Reconnect just in case the connection was lost
  connectDatabase($db_host, $db_user, $db_pwd, $db_dbname);
  
  // Store the results
  storeResults($db_tablename, $object_property, $object_id, $results);
}
  
/**
 * Local Database Connection
 */  
function connectDatabase($db_host, $db_user, $db_pwd, $db_dbname) {
  mysql_connect($db_host, $db_user, $db_pwd) or die("Could not connect to database server: " . mysql_error()); 
  mysql_select_db($db_dbname) or die("Could select database: " . mysql_error()); 
}

/**
 * Makes sure the table definitions are present
 */
function prepareTables($db_tablename) {
  // TODO: Use an enum for object_property with the different object properties that you are fetching, if these are not
  //       likely to change
  mysql_query("
    CREATE TABLE IF NOT EXISTS `".$db_tablename."` (
      `object_id` int(11) unsigned NOT NULL,
      `object_property` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `timestamp` bigint(20) unsigned NOT NULL,
      `value` float NOT NULL,
      PRIMARY KEY (`object_id`,`object_property`,`timestamp`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  ") or die("Could create measurements table: " . mysql_error()); 
}

/**
 * Queries the Munisense REST Webservice
 * 
 * @returns Array with results
 */
function queryWebservice($muni_host, $muni_user, $muni_pwd, $object_type, $object_property, $object_id, $start_timestamp) {
  $url = "https://".$muni_host."/webservices/v1/".$object_type."/".$object_id."/".$object_property."/query?starttime=".$start_timestamp."&order_field=timestamp&order_dir=asc&rowcount=4000";
  echo $url."\n";
  $curl_options = array(CURLOPT_HEADER => 0,
                    CURLOPT_URL => $url,
                    CURLOPT_USERPWD => $muni_user.":".$muni_pwd,
                    CURLOPT_FRESH_CONNECT => 1,
                    CURLOPT_RETURNTRANSFER => 1,
                    CURLOPT_FORBID_REUSE => 1,
                    CURLOPT_TIMEOUT => 10);


  $ch = curl_init();
  curl_setopt_array($ch, $curl_options);

  if(!$result = curl_exec($ch))
    trigger_error(curl_error($ch));

  curl_close($ch);
  
  $decoded_data = json_decode($result);

  $decoded_results = $decoded_data->{$object_type."PropertyQuery"};

  $results = array();

  // JSON returns single element arrays not as arrays but as object, so a convert is needed
  // for the case only one result is returned.
  if(isset($decoded_results->Result) && !is_array($decoded_results->Result))
    $decoded_results->Result = array($decoded_results->Result);

  // If result is not set, then there are no new results  
  if(isset($decoded_results->Result)) {
    foreach($decoded_results->Result as $decoded_result) {
      $results[$decoded_result->timestamp] = $decoded_result->value->{"@t"};
    }
  }

  return $results;
}    

/**
 * Stores the Results in the local SQL database
 */
function storeResults($db_tablename, $object_property, $object_id, $results) {
  $query_base = "INSERT INTO `".$db_tablename."` (`object_id`, `object_property`, `timestamp`, `value`) VALUES ";

  // Make chunks of results of size 100
  $results_chunks = array_chunk($results, 100, true);

  foreach($results_chunks as $results_chunk) {
    $query = $query_base;

    $data_query_elements = array();
    foreach($results_chunk as $result_timestamp => $result_value)
      $data_query_elements[] = "(".$object_id.",'".$object_property."',".$result_timestamp.",'".$result_value."')";
    
    // Join the array of result query parts into one big insert query containing 100 results
    $query .= implode(",", $data_query_elements);

    // Execute the insert query
    mysql_query($query) or die("Could not insert results: " . mysql_error());
  }
}

?>
